package com.excel.sys.service.impl;


import com.excel.sys.service.ExportExcelService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.stereotype.Service;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Service
public class ExportExcelServiceImpl implements ExportExcelService {

    //根据模板导出 Excel 表格
    @Override
    public Workbook exportExcel() throws IOException {
        //加载 Excel 模板
        Resource resource =  new ClassPathResource("excel-template/mode.xlsx");
        FileInputStream fis = new FileInputStream(resource.getFile());
        //根据模板 创建工作薄
        Workbook wb = new XSSFWorkbook(fis);
        //读取工作表
        Sheet sheet = wb.getSheetAt(0);
        //抽取公共样式
        Row row = sheet.getRow(2);
        CellStyle[] style = new CellStyle[row.getLastCellNum()];
        for (int i = 0; i < style.length ; i++) {
            Cell cell = row.getCell(i);
            style[i] = cell.getCellStyle();
        }
        //获取单元格数据
        List<Map<String,String>> userInfo = userInfo();
        //构造并编辑单元格
        Cell cell = null;
        for (int i = 0; i < userInfo.size() ; i++) {
            row =  sheet.createRow(i+2);

            cell = row.createCell(0);
            cell.setCellValue(userInfo.get(i).get("id"));
            cell.setCellStyle(style[0]);

            cell = row.createCell(1);
            cell.setCellValue(userInfo.get(i).get("name"));
            cell.setCellStyle(style[1]);

            cell = row.createCell(2);
            cell.setCellValue(userInfo.get(i).get("password"));
            cell.setCellStyle(style[2]);

        }
        return wb;
    }

    @Override
    public Workbook exportExcel2() throws IOException {
        List<Map<String,Object>> users = new ArrayList<>();
        Map<String,Object> user=new HashMap<>();
        for (int i = 0; i < 11 ; i++) {
            user = new HashMap<>();
            user.put("id", i + 1 + "");
            user.put("name", "name" + (i + 1));
            user.put("password", "password" + (i + 1));
            List<Map<String, Object>> cs = new ArrayList<>();
            for (int j = 0; j < 2; j++) {
                Map<String, Object> c = new HashMap<>();
                c.put("cname", "cname" + j);
                c.put("cage", "cage" + j);
                c.put("cp", "cp" + j);
                cs.add(c);
            }
            user.put("cs", cs);
            users.add(user);
        }
        //加载 Excel 模板
        Resource resource =  new ClassPathResource("excel-template/mode.xlsx");
        FileInputStream fis = new FileInputStream(resource.getFile());
        //根据模板 创建工作薄
        Workbook wb = new XSSFWorkbook(fis);
        //读取工作表
        Sheet sheet = wb.getSheetAt(0);
        Row row = sheet.getRow(2);
        CellStyle[] style = new CellStyle[row.getLastCellNum()];
        for (int i = 0; i < style.length ; i++) {
            Cell cell = row.getCell(i);
            style[i] = cell.getCellStyle();
        }
        int last = 2;
        int w = 0;
        Cell cell = null;
        for (int i = 0; i < users.size(); i++) {
            List<Map<String,Object>> cs = (List<Map<String, Object>>) users.get(i).get("cs");
            row =  sheet.createRow(last++);
            cell = row.createCell(0);
            cell.setCellValue(users.get(i).get("id").toString());
            cell.setCellStyle(style[0]);
            cell = row.createCell(2);
            cell.setCellValue(users.get(i).get("name").toString());
            cell.setCellStyle(style[0]);
            int q = last;
            for (int j = 0; j < cs.size(); j++) {

                if (w!=0){
                    row =  sheet.createRow(last++);
                    cell = row.createCell(0);
                    cell.setCellValue("");
                    cell.setCellStyle(style[0]);
                    cell = row.createCell(1);
                    cell.setCellValue(cs.get(j).get("cname").toString());
                    cell.setCellStyle(style[0]);
                    cell = row.createCell(2);
                    cell.setCellValue("");
                    cell.setCellStyle(style[0]);
                }else {

                    cell = row.createCell(1);
                    cell.setCellValue(cs.get(j).get("cname").toString());
                    cell.setCellStyle(style[0]);

                    w=1;
                }
            }
            w=0;
            int h = last;
            CellRangeAddress region = new CellRangeAddress(q-1,h-1,0,0);
            CellRangeAddress region2 = new CellRangeAddress(q-1,h-1,2,2);
            sheet.addMergedRegion(region);
            sheet.addMergedRegion(region2);
        }

        return wb;
    }

    //假造数据
    public List<Map<String,String>> userInfo (){
        List<Map<String,String>> users = new ArrayList<>();
        Map<String,String> user=null;
        for (int i = 0; i < 11 ; i++) {
            user = new HashMap<>();
            user.put("id",i+1+"");
            user.put("name","name"+(i+1));
            user.put("password","password"+(i+1));
            users.add(user);
        }
        return users;
    }

    //假造数据2
    public List<Map<String,Object>> userInfo2 (){
        List<Map<String,Object>> users = new ArrayList<>();
        Map<String,Object> user=new HashMap<>();
        for (int i = 0; i < 11 ; i++) {
            user = new HashMap<>();
            user.put("id",i+1+"");
            user.put("name","name"+(i+1));
            user.put("password","password"+(i+1));
            List<Map<String,Object>> cs = new ArrayList<>();
            for (int j = 0; j <2 ; j++) {
                Map<String,Object> c = new HashMap<>();
                c.put("cname","cname"+j);
                c.put("cage","cage"+j);
                c.put("cp","cp"+j);
                cs.add(c);
            }
            user.put("cs",cs);
            users.add(user);
        }
        return users;
    }

}




























